{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-colab"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/pathwaycom/pathway/blob/main/examples/notebooks/tutorials/groupby_reduce_manual.ipynb\" target=\"_parent\"><img src=\"https://pathway.com/assets/colab-badge.svg\" alt=\"Run In Colab\" class=\"inline\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "notebook-instructions",
      "source": [
        "# Installing Pathway with Python 3.10+\n",
        "\n",
        "In the cell below, we install Pathway into a Python 3.10+ Linux runtime.\n",
        "\n",
        "> **If you are running in Google Colab, please run the colab notebook (Ctrl+F9)**, disregarding the 'not authored by Google' warning.\n",
        "> \n",
        "> **The installation and loading time is less than 1 minute**.\n"
      ],
      "metadata": {}
    },
    {
      "cell_type": "code",
      "id": "pip-installation-pathway",
      "source": [
        "%%capture --no-display\n",
        "!pip install --prefer-binary pathway"
      ],
      "execution_count": null,
      "outputs": [],
      "metadata": {}
    },
    {
      "cell_type": "code",
      "id": "logging",
      "source": [
        "import logging\n",
        "\n",
        "logging.basicConfig(level=logging.CRITICAL)"
      ],
      "execution_count": null,
      "outputs": [],
      "metadata": {}
    },
    {
      "cell_type": "markdown",
      "id": "4",
      "metadata": {},
      "source": [
        "# Groupby - Reduce\n",
        "In this manu\\[a\\]l, you will learn how to aggregate data with the groupby-reduce scheme.\n",
        "\n",
        "Together, the [`groupby`](/developers/api-docs/pathway-table#pathway.Table.groupby) and [`reduce`](/developers/api-docs/pathway-table#pathway.Table.reduce) operations can be used\n",
        "to **aggregate data across the rows of the table**. In this guide,\n",
        "we expand upon a simple demonstration from the\n",
        "[First-steps Guide](/developers/user-guide/data-transformation/table-operations)\n",
        "and:\n",
        "* explain syntax of [groupby](#groupby-syntax) and [reduce](#reduce-syntax)\n",
        "* explain [two kinds of columns we get from groupby](#groupby-reduce-column-constrains)\n",
        "* explain [automatic id generation](#auto-generated-id)\n",
        "* show [a few simple applications](#more-examples)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "5",
      "metadata": {},
      "source": [
        "## Prerequisites\n",
        "The assumption is that we are familiar with some basic operations\n",
        "explained in the [First-steps Guide](/developers/user-guide/data-transformation/table-operations).\n",
        "As usual, we begin with importing Pathway."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "6",
      "metadata": {},
      "outputs": [],
      "source": [
        "import pathway as pw"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "7",
      "metadata": {
        "lines_to_next_cell": 2
      },
      "outputs": [],
      "source": [
        "# To use advanced features with Pathway Scale, get your free license key from\n",
        "# https://pathway.com/features and paste it below.\n",
        "# To use Pathway Community, comment out the line below.\n",
        "pw.set_license_key(\"demo-license-key-with-telemetry\")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "8",
      "metadata": {
        "lines_to_next_cell": 2
      },
      "source": [
        "To demonstrate the capabilities of groupby and reduce operations,\n",
        "let us consider a made up scenario.\n",
        "\n",
        "**Storyline:** let's assume that you made a poll, asking whether a particular food item is a fruit or a vegetable.\n",
        "\n",
        "An answer to such a question is a tuple `(food_item, label, vote, fractional_vote)`.\n",
        "That is, if someone could tell that tomato is a fruit, but they are not really sure,\n",
        "it could be registered as two tuples:\n",
        "\n",
        "* (tomato, fruit, 1, 0.5),\n",
        "\n",
        "* (tomato, vegetable, 0, 0.5)\n",
        "\n",
        "Below, we have the results of the poll, stored in the table *poll*."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "9",
      "metadata": {},
      "outputs": [],
      "source": [
        "poll = pw.debug.table_from_markdown(\n",
        "    \"\"\"\n",
        "    | food_item | label     | vote  | fractional_vote   | time\n",
        "0   | tomato    | fruit     | 1     | 0.5               | 1669882728\n",
        "1   | tomato    | vegetable | 0     | 0.5               | 1669882728\n",
        "2   | apple     | fruit     | 1     | 1                 | 1669883612\n",
        "3   | apple     | vegetable | 0     | 0                 | 1669883612\n",
        "4   | pepper    | vegetable | 1     | 0.5               | 1669883059\n",
        "5   | pepper    | fruit     | 0     | 0.5               | 1669883059\n",
        "6   | tomato    | fruit     | 0     | 0.3               | 1669880159\n",
        "7   | tomato    | vegetable | 1     | 0.7               | 1669880159\n",
        "8   | corn      | fruit     | 0     | 0.3               | 1669876829\n",
        "9   | corn      | vegetable | 1     | 0.7               | 1669876829\n",
        "10  | tomato    | fruit     | 0     | 0.4               | 1669874325\n",
        "11  | tomato    | vegetable | 1     | 0.6               | 1669874325\n",
        "12  | pepper    | fruit     | 0     | 0.45              | 1669887207\n",
        "13  | pepper    | vegetable | 1     | 0.55              | 1669887207\n",
        "14  | apple     | fruit     | 1     | 1                 | 1669874325\n",
        "15  | apple     | vegetable | 0     | 0                 | 1669874325\n",
        "\n",
        "\"\"\"\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "10",
      "metadata": {},
      "source": [
        "To demonstrate a simple groupby-reduce application, let's ask about\n",
        "the total `fractional_vote` that was assigned to any combination of `foot_item`, `label`.\n",
        "\n",
        "\n",
        "First we explain the syntax of both [`groupby`](#groupby-syntax) and [`reduce`](#reduce-syntax).\n",
        "Then, we show groupby-reduce code in [action](#groupby-reduce-simple-example)."
      ]
    },
    {
      "cell_type": "markdown",
      "id": "11",
      "metadata": {},
      "source": [
        "## Groupby Syntax\n",
        "The syntax of the [`groupby`](/developers/api-docs/pathway-table#pathway.Table.groupby) operation is fairly simple:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "12",
      "metadata": {},
      "outputs": [],
      "source": [
        "table.groupby(*C)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "13",
      "metadata": {},
      "source": [
        "\n",
        "It takes a list of columns `*C` as argument and groups the row according to their values in those columns.\n",
        "In other words, all the rows with the same values, column-wise, in each column of `*C` are put into the same group.\n",
        "\n",
        "As a result, it returns a [`GroupedTable`](/developers/api-docs/pathway#pathway.GroupedTable) object, which stores\n",
        "a single row for each unique tuple from columns in `*C` and a collection\n",
        "of grouped items corresponding to each column that is not in `*C`.\n",
        "\n",
        "In the example above, if we groupby over a pair of columns `food_item`, `label`,\n",
        "the groupby computes a collection of `votes` and a collection of `fractional_votes`\n",
        "for each unique combination `food_item`, `label`.\n",
        "\n",
        "In order to use this object, we need to process those collections\n",
        "with the `reduce` operation.\n",
        "\n",
        "## Reduce Syntax\n",
        "The [`reduce`](/developers/api-docs/pathway#pathway.GroupedTable.reduce) function behaves a little bit like [`select`](/developers/api-docs/pathway-table#pathway.Table.select), and it also takes\n",
        "two kinds of arguments:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "14",
      "metadata": {},
      "outputs": [],
      "source": [
        "grouped_table.reduce(*SC, *NC)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "15",
      "metadata": {},
      "source": [
        "* *SC is simply a list of columns that were present in the table before the groupby\n",
        "operation,\n",
        "* *NC is a list of new columns (i.e. columns with new name), each defined as\n",
        "some function of cells in the row of grouped_table.\n",
        "\n",
        "It can be used together with groupby, as follows:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "16",
      "metadata": {},
      "outputs": [],
      "source": [
        "table.groupby(*C).reduce(*SC, *NC)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "17",
      "metadata": {},
      "source": [
        "The main difference between `reduce` and `select` is that each row of `grouped_table`\n",
        "has two kinds of entries, simple cells and groups.\n",
        "\n",
        "The `reduce` operation allows us to apply a reducer to transform a group into a value.\n",
        "\n",
        "## Counting Votes With Groupby-Reduce\n",
        "Below, you can see an example that uses the [`sum`](/developers/api-docs/reducers#pathway.reducers.sum) reducer to compute the sum of all\n",
        "votes and the sum of all fractional votes."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "18",
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "            | food_item | label     | total_votes | total_fractional_vote\n",
            "^8X6FZAN... | apple     | fruit     | 2           | 2.0\n",
            "^1PM8GGB... | apple     | vegetable | 0           | 0.0\n",
            "^Y7P7Z85... | corn      | fruit     | 0           | 0.3\n",
            "^R5DPRJ1... | corn      | vegetable | 1           | 0.7\n",
            "^MPW1XV7... | pepper    | fruit     | 0           | 0.95\n",
            "^TW138S3... | pepper    | vegetable | 2           | 1.05\n",
            "^Z7NB38W... | tomato    | fruit     | 1           | 1.2\n",
            "^E3Z6HDV... | tomato    | vegetable | 2           | 1.7999999999999998\n"
          ]
        }
      ],
      "source": [
        "aggregated_results = poll.groupby(poll.food_item, poll.label).reduce(\n",
        "    poll.food_item,\n",
        "    poll.label,\n",
        "    total_votes=pw.reducers.sum(poll.vote),\n",
        "    total_fractional_vote=pw.reducers.sum(poll.fractional_vote),\n",
        ")\n",
        "\n",
        "pw.debug.compute_and_print(aggregated_results)\n"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "19",
      "metadata": {
        "lines_to_next_cell": 2
      },
      "source": [
        "## Groupby-Reduce Column Constraints\n",
        "To briefly summarize, if $T$ is the set of all columns of a table\n",
        "* the columns from $C$ (used as comparison key) can be used as regular columns in the reduce function\n",
        "* for all the remaining columns (in $T \\setminus C$), we need to apply a reducer, before we use them in expressions\n",
        "\n",
        "In particular, we can mix columns from $C$ and reduced columns from $T \\setminus C$ in column expressions."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "20",
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "            | food_item | note\n",
            "^8X6FZAN... | apple     | fruit got 2 votes, with total fractional_vote of 2.0\n",
            "^1PM8GGB... | apple     | vegetable got 0 votes, with total fractional_vote of 0.0\n",
            "^Y7P7Z85... | corn      | fruit got 0 votes, with total fractional_vote of 0.3\n",
            "^R5DPRJ1... | corn      | vegetable got 1 votes, with total fractional_vote of 0.7\n",
            "^MPW1XV7... | pepper    | fruit got 0 votes, with total fractional_vote of 0.95\n",
            "^TW138S3... | pepper    | vegetable got 2 votes, with total fractional_vote of 1.05\n",
            "^Z7NB38W... | tomato    | fruit got 1 votes, with total fractional_vote of 1.2\n",
            "^E3Z6HDV... | tomato    | vegetable got 2 votes, with total fractional_vote of 1.8\n"
          ]
        }
      ],
      "source": [
        "def make_a_note(label: str, tot_votes: int, tot_fractional_vote: float):\n",
        "    return f\"{label} got {tot_votes} votes, with total fractional_vote of {round(tot_fractional_vote, 2)}\"\n",
        "\n",
        "\n",
        "aggregated_results_note = poll.groupby(poll.food_item, poll.label).reduce(\n",
        "    poll.food_item,\n",
        "    note=pw.apply(\n",
        "        make_a_note,\n",
        "        poll.label,\n",
        "        pw.reducers.sum(poll.vote),\n",
        "        pw.reducers.sum(poll.fractional_vote),\n",
        "    ),\n",
        ")\n",
        "\n",
        "pw.debug.compute_and_print(aggregated_results_note)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "21",
      "metadata": {},
      "source": [
        "## Auto generated id\n",
        "The `groupby(*C).reduce(...)` operation guarantees that each row of the\n",
        "output corresponds to a unique tuple of values from *C. Therefore *C\n",
        "can be used to generate a unique id for the resulting table.\n",
        "\n",
        "In fact that is the default behavior of Pathway' groupby operation and can be used e.g. to\n",
        "join the results of this table with some other table that has this id as a foreign key."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "22",
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "            | food_item | note\n",
            "^R5DPRJ1... | corn      | vegetable got 1 votes, with total fractional_vote of 0.7\n",
            "^TW138S3... | pepper    | vegetable got 2 votes, with total fractional_vote of 1.05\n",
            "^Z7NB38W... | tomato    | fruit got 1 votes, with total fractional_vote of 1.2\n"
          ]
        }
      ],
      "source": [
        "queries = pw.debug.table_from_markdown(\n",
        "    \"\"\"\n",
        "    | food_item | label\n",
        "1   | tomato    | fruit\n",
        "2   | pepper    | vegetable\n",
        "3   | corn      | vegetable\n",
        "\n",
        "\"\"\"\n",
        ").with_id_from(pw.this.food_item, pw.this.label)\n",
        "\n",
        "pw.debug.compute_and_print(\n",
        "    queries.join(\n",
        "        aggregated_results_note, queries.id == aggregated_results_note.id, id=queries.id\n",
        "    ).select(queries.food_item, aggregated_results_note.note)\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "23",
      "metadata": {},
      "source": [
        "More examples of joins (including another example of a join over a foreign key)\n",
        "can be found in the join manual ([full article](/developers/user-guide/data-transformation/join-manual), [foreign key example](/developers/user-guide/data-transformation/join-manual#joins-on-a-foreign-key)).\n"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "24",
      "metadata": {},
      "source": [
        "## More Examples\n",
        "### Recent activity with max reducer\n",
        "Below, you can see a piece of code that finds the latest votes that were submitted to the poll.\n",
        "It is done with `groupby`-`reduce` operations chained with [`join`](/developers/api-docs/pathway-table#pathway.Table.join) and [`filter`](/developers/api-docs/pathway-table#pathway.Table.filter), using [`pw.this`](/developers/api-docs/pathway#pathway.this).\n",
        "\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "25",
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "            | food_item | label     | vote | fractional_vote\n",
            "^Z3QWT29... | apple     | fruit     | 1    | 1.0\n",
            "^3CZ78B4... | apple     | vegetable | 0    | 0.0\n",
            "^GFDBR0G... | pepper    | fruit     | 0    | 0.45\n",
            "^3S2X6B2... | pepper    | fruit     | 0    | 0.5\n",
            "^3HN31E1... | pepper    | vegetable | 1    | 0.5\n",
            "^3J2R55X... | pepper    | vegetable | 1    | 0.55\n",
            "^A984WV0... | tomato    | fruit     | 0    | 0.3\n",
            "^X1MXHYY... | tomato    | fruit     | 1    | 0.5\n",
            "^YYY4HAB... | tomato    | vegetable | 0    | 0.5\n",
            "^6A0QZMJ... | tomato    | vegetable | 1    | 0.7\n"
          ]
        }
      ],
      "source": [
        "hour = 3600\n",
        "pw.debug.compute_and_print(\n",
        "    poll.groupby()\n",
        "    .reduce(time=pw.reducers.max(poll.time))\n",
        "    .join(poll, id=poll.id)\n",
        "    .select(\n",
        "        poll.food_item,\n",
        "        poll.label,\n",
        "        poll.vote,\n",
        "        poll.fractional_vote,\n",
        "        poll.time,\n",
        "        latest=pw.left.time,\n",
        "    )\n",
        "    .filter(pw.this.time >= pw.this.latest - 2 * hour)\n",
        "    .select(\n",
        "        pw.this.food_item,\n",
        "        pw.this.label,\n",
        "        pw.this.vote,\n",
        "        pw.this.fractional_vote,\n",
        "    )\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "26",
      "metadata": {},
      "source": [
        "### Removing duplicates\n",
        "Below, duplicates are removed from the table with groupby-reduce.\n",
        "On its own, selecting `food_item` and `label` from poll returns duplicate rows:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "27",
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "            | food_item | label\n",
            "^XTPZRQ2... | apple     | fruit\n",
            "^Z3QWT29... | apple     | fruit\n",
            "^3CZ78B4... | apple     | vegetable\n",
            "^MA4SK6C... | apple     | vegetable\n",
            "^SN0FH7F... | corn      | fruit\n",
            "^9KM937R... | corn      | vegetable\n",
            "^GFDBR0G... | pepper    | fruit\n",
            "^3S2X6B2... | pepper    | fruit\n",
            "^3HN31E1... | pepper    | vegetable\n",
            "^3J2R55X... | pepper    | vegetable\n",
            "^X1MXHYY... | tomato    | fruit\n",
            "^QECDZJF... | tomato    | fruit\n",
            "^A984WV0... | tomato    | fruit\n",
            "^YYY4HAB... | tomato    | vegetable\n",
            "^6A0QZMJ... | tomato    | vegetable\n",
            "^03PYXDQ... | tomato    | vegetable\n"
          ]
        }
      ],
      "source": [
        "pw.debug.compute_and_print(poll.select(poll.food_item, poll.label))"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "28",
      "metadata": {},
      "source": [
        "However, we can apply groupby-reduce to select a set of unique rows:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "29",
      "metadata": {
        "lines_to_next_cell": 2
      },
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "            | food_item | label\n",
            "^8X6FZAN... | apple     | fruit\n",
            "^1PM8GGB... | apple     | vegetable\n",
            "^Y7P7Z85... | corn      | fruit\n",
            "^R5DPRJ1... | corn      | vegetable\n",
            "^MPW1XV7... | pepper    | fruit\n",
            "^TW138S3... | pepper    | vegetable\n",
            "^Z7NB38W... | tomato    | fruit\n",
            "^E3Z6HDV... | tomato    | vegetable\n"
          ]
        }
      ],
      "source": [
        "pw.debug.compute_and_print(\n",
        "    poll.groupby(poll.food_item, poll.label).reduce(poll.food_item, poll.label)\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "30",
      "metadata": {},
      "source": [
        "### Chained groupby-reduce-join-select\n",
        "Below, you can find an example of groupby - reduce chained with join -select,\n",
        "using `pw.this`.\n",
        "\n",
        "To demonstrate that, we can ask our poll about total fractional vote for each pair\n",
        "`food_item`, `label` and total fractional vote assigned to rows for each `food_item`."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "31",
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "            | food_item | label     | label_fractional_vote | total_fractional_vote\n",
            "^7YTTW8S... | apple     | fruit     | 2.0                   | 2.0\n",
            "^7YTJZBX... | apple     | vegetable | 0.0                   | 2.0\n",
            "^PHR71C0... | corn      | fruit     | 0.3                   | 1.0\n",
            "^PHR3860... | corn      | vegetable | 0.7                   | 1.0\n",
            "^C5046A2... | pepper    | fruit     | 0.95                  | 2.0\n",
            "^C504ZMA... | pepper    | vegetable | 1.05                  | 2.0\n",
            "^NHNP79E... | tomato    | fruit     | 1.2                   | 3.0\n",
            "^NHNGEC4... | tomato    | vegetable | 1.7999999999999998    | 3.0\n"
          ]
        }
      ],
      "source": [
        "relative_score = (\n",
        "    poll.groupby(poll.food_item)\n",
        "    .reduce(\n",
        "        poll.food_item,\n",
        "        total_fractional_vote=pw.reducers.sum(poll.fractional_vote),\n",
        "    )\n",
        "    .join(aggregated_results, pw.left.food_item == pw.right.food_item)\n",
        "    .select(\n",
        "        pw.left.food_item,\n",
        "        pw.right.label,\n",
        "        label_fractional_vote=pw.right.total_fractional_vote,\n",
        "        total_fractional_vote=pw.left.total_fractional_vote,\n",
        "    )\n",
        ")\n",
        "\n",
        "pw.debug.compute_and_print(relative_score)"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "32",
      "metadata": {},
      "source": [
        "### Election using argmax reducer\n",
        "Below, we present a snippet of code, that in the context of a poll,\n",
        "finds the most obvious information: which label got the most votes."
      ]
    },
    {
      "cell_type": "markdown",
      "id": "33",
      "metadata": {},
      "source": [
        "Let's take a look on what exactly is the result of [`argmax`](/developers/api-docs/reducers#pathway.reducers.argmax) reducer:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "34",
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "            | argmax_id\n",
            "^NHNKR02... | ^NHNGEC4...\n",
            "^7YTG4C2... | ^7YTTW8S...\n",
            "^C50B2KX... | ^C504ZMA...\n",
            "^PHR4QTJ... | ^PHR3860...\n"
          ]
        }
      ],
      "source": [
        "pw.debug.compute_and_print(\n",
        "    relative_score.groupby(relative_score.food_item).reduce(\n",
        "        argmax_id=pw.reducers.argmax(relative_score.label_fractional_vote)\n",
        "    )\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "35",
      "metadata": {},
      "source": [
        "As you can see, it returns an ID of the row that maximizes `label_fractional_vote`\n",
        "for a fixed `food_item`.\n",
        "You can filter interesting rows using those ID-s as follows:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "36",
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "            | food_item | label     | label_fractional_vote\n",
            "^0AVWY3X... | apple     | fruit     | 2.0\n",
            "^EF480M4... | corn      | vegetable | 0.7\n",
            "^DV5NNAH... | pepper    | vegetable | 1.05\n",
            "^S8WYQGD... | tomato    | vegetable | 1.7999999999999998\n"
          ]
        }
      ],
      "source": [
        "pw.debug.compute_and_print(\n",
        "    relative_score.groupby(relative_score.food_item)\n",
        "    .reduce(argmax_id=pw.reducers.argmax(relative_score.label_fractional_vote))\n",
        "    .join(relative_score, pw.left.argmax_id == relative_score.id)\n",
        "    .select(\n",
        "        relative_score.food_item,\n",
        "        relative_score.label,\n",
        "        relative_score.label_fractional_vote,\n",
        "    )\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "37",
      "metadata": {},
      "source": [
        "*Remark:* the code snippet above is equivalent to:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "id": "38",
      "metadata": {},
      "outputs": [
        {
          "name": "stdout",
          "output_type": "stream",
          "text": [
            "            | food_item | label     | label_fractional_vote\n",
            "^7YTG4C2... | apple     | fruit     | 2.0\n",
            "^PHR4QTJ... | corn      | vegetable | 0.7\n",
            "^C50B2KX... | pepper    | vegetable | 1.05\n",
            "^NHNKR02... | tomato    | vegetable | 1.7999999999999998\n"
          ]
        }
      ],
      "source": [
        "pw.debug.compute_and_print(\n",
        "    relative_score.groupby(relative_score.food_item)\n",
        "    .reduce(argmax_id=pw.reducers.argmax(relative_score.label_fractional_vote))\n",
        "    .select(\n",
        "        relative_score.ix(pw.this.argmax_id).food_item,\n",
        "        relative_score.ix(pw.this.argmax_id).label,\n",
        "        relative_score.ix(pw.this.argmax_id).label_fractional_vote,\n",
        "    )\n",
        ")"
      ]
    },
    {
      "cell_type": "markdown",
      "id": "39",
      "metadata": {},
      "source": [
        "You can read more about [joins](/developers/user-guide/data-transformation/join-manual), [*.ix](/developers/api-docs/pathway#property-ix) and [ID-s](/developers/user-guide/data-transformation/table-operations#manipulating-the-table) in other places."
      ]
    }
  ],
  "metadata": {
    "jupytext": {
      "cell_metadata_filter": "-all",
      "main_language": "python",
      "notebook_metadata_filter": "-all"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.11.14"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 5
}